package com.uncp.comisiondeadmision.carnet.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;

import com.uncp.comisiondeadmision.carnet.beans.CarnetBean;
import com.uncp.comisiondeadmision.carnet.beans.CarnetPostuMixBean;
import com.uncp.comisiondeadmision.comprobantedepago.beans.PostulanteBean;
import com.uncp.comisiondeadmision.util.MySqlDBConn;

public class MysqlCarnetDao implements CarnetDAO {

	public ArrayList<PostulanteBean> consultaActivos() throws SQLException {
	
		Connection con = null;
		Statement s =null;
		ResultSet rs = null;	
	    String pendiente = "Pendiente";
	    String aprobado = "aprobado";
	    ArrayList<PostulanteBean> array = new ArrayList<PostulanteBean>(); 
		
		try {
			con = new MySqlDBConn().getConnection();
			s = con.createStatement();
			String sql = "select p.CODPOSTULANTE,p.NOMBRES,p.APEPAT,p.APEMAT,p.ESTADO " +
					              "  from postulante p join solicitud s  "+
			                      "  on p.codpostulante = s.codpostulante " +
					              "  where p.ESTADO='"+pendiente+"' and s.estado='"+aprobado+"'";
			rs = s.executeQuery(sql);
			
			System.out.println("lista cliente :"+sql);
			PostulanteBean bean;
			
			while(rs.next()){
				
		    bean = new PostulanteBean();
		    bean.setIntCodPostulante(rs.getInt(1));
			bean.setStrNombres(rs.getString(2));
			bean.setStrApepat(rs.getString(3));
			bean.setStrApemat(rs.getString(4));
			bean.setStrEstado(rs.getString(5));
			array.add(bean);	
								
			}
	
		} catch (Exception e) {
			System.out.println(e.getMessage());
		}
		finally{
			try {
				if(con!=null)con.close();
				if(s!=null)s.close();
				if(rs!=null)rs.close();
			} catch (Exception e2) {
				System.out.println(e2.getMessage());
			}
		}

		return array;
	}

	public PostulanteBean consultaXCodigo(int Codigo,String estado) throws SQLException {
	
		Connection con = null;
		Statement s = null;
		ResultSet rs = null;
        String aprobado = "aprobado";
		PostulanteBean bean = null;
		
		try {
			
		con = new  MySqlDBConn().getConnection();
		
		String sql = "select p.CODPOSTULANTE,p.NOMBRES,p.APEPAT,p.APEMAT,p.ESTADO,p.FOTO"+ 
			                " from postulante p join solicitud s "+	
			                " on p.codpostulante = s.codpostulante" +
                            " where  p.CODPOSTULANTE = '"+Codigo+"' and p.ESTADO = '"+estado+"' and s.estado = '"+aprobado+"'";

		
		s = con.createStatement();
		rs = s.executeQuery(sql);
		System.out.println("Consulta x Codigo :"+sql);	
		while(rs.next()){
			
			bean = new PostulanteBean();
			bean.setIntCodPostulante(rs.getInt(1));
			bean.setStrNombres(rs.getString(2));
			System.out.println(rs.getString(3));
			bean.setStrApepat(rs.getString(3));
			bean.setStrApemat(rs.getString(4));
		    bean.setStrEstado(rs.getString(5));
		    bean.setBlobImagen(rs.getBytes(6));

		  }		
		} catch (Exception e) {
			System.out.println(e.getMessage());
		}
		finally{
			try {
				
				if(con!=null)con.close();
				if(s!=null)s.close();
				if(rs!=null)rs.close();
				
			} catch (Exception e2) {
				System.out.println(e2.getMessage());
			}
		}
		
		return bean;
	}

	public boolean actualizaEstado(int codigoPostu) throws SQLException {
	
		Connection con = null;
		Statement s = null;
		String actualizarEstado = "Activo";
		try {
			con = new  MySqlDBConn().getConnection();
			String sql = "update postulante set ESTADO ='"+actualizarEstado+
			                    "' where CODPOSTULANTE ='"+codigoPostu+"'";
			s = con.createStatement();
			
			System.out.println("ActulizaEstado :"+sql);
			
			return s.execute(sql);

		} catch (Exception e) {
			System.out.println(e.getMessage());
		}
		finally{
			try {
				
				if(con!=null)con.close();
				if(s!=null)s.close();
				
			} catch (Exception e2) {
				System.out.println(e2.getMessage());
			}
		}
		
		return false;
	}

	public int insertarCarnet(CarnetBean bean) throws SQLException {
		
		SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd hh:mm:ss");
		
		Connection con = null;
		PreparedStatement ps = null;
		
		try {
			
			con = new  MySqlDBConn().getConnection();
			String sql = "insert into carnet values(null,?,?,?)";
			ps = con.prepareStatement(sql);
		  
		//	ps.setInt(1, bean.getIntCodCarnet()); 
			ps.setString(1, bean.getStrEstaCarnet());
			ps.setString(2,sdf.format(new Date()));
			ps.setInt(3,bean.getIntCodPostu());
			
			System.out.println("registra carnet :"+ps);
			
			return ps.executeUpdate();
							
		} catch (Exception e) {
			System.out.println(e.getMessage());
		}
		finally{
			try {
				if(con!=null)con.close();
				if(ps!=null)ps.close();
			} catch (Exception e2) {
				System.out.println(e2.getMessage());
			}
			
		}
	
		return -1;
	}

	public int codigoGenerado() throws SQLException {
	
		Connection con = null;
		Statement s = null;
		ResultSet rs = null;
		//order by codcarnet desc limit 1
		String query = "select codcarnet from carnet order by codcarnet desc limit 1"; 
		try{		
		      	con = new  MySqlDBConn().getConnection();
		      	s = con.createStatement();
		      	rs = s.executeQuery(query);
		      	rs.next();
		        int codigo = rs.getInt(1);
		    
		        System.out.println("Codigo Generado :"+codigo);
		        
			    return codigo;
					
		} catch (Exception e) {
			System.out.println(e.getMessage());	
		}finally{
			try {
				if(con!=null)con.close();
				if(s!=null)s.close();
				if(rs!=null)rs.close();
			} catch (Exception e2) {
				System.out.println(e2.getMessage());
			}
			
		}
       
		return -1;
	}

	public ArrayList<CarnetPostuMixBean> listaPostuConCarnet() throws SQLException {
		
		Connection con = null;
		Statement s =null;
		ResultSet rs = null;	
	    String Generado = "Generado";
	    ArrayList<CarnetPostuMixBean> array = new ArrayList<CarnetPostuMixBean>(); 
		
		try {
			con = new  MySqlDBConn().getConnection();
			s = con.createStatement();
			String sql = "select  p.codpostulante,p.nombres,p.apepat,p.apemat,c.estado "+
			                      " from carnet c join postulante p " +
			                      " on c.codpostulante = p.codpostulante "+
			                      "  where c.estado = '"+Generado+"'";
			rs = s.executeQuery(sql);
			
			System.out.println("listaPostuConCarnet() :"+sql);
			CarnetPostuMixBean bean;
			
			while(rs.next()){
				
		    bean = new CarnetPostuMixBean();
		    bean.setIntCodPostuMix(rs.getInt(1));
		    bean.setStrNombreMix(rs.getString(2));
			bean.setStrApePaterMix(rs.getString(3));
			bean.setStrApeMaterMix(rs.getString(4));
			bean.setStrEstaCarnetMix(rs.getString(5));
			array.add(bean);	
								
			}
	
		} catch (Exception e) {
			System.out.println(e.getMessage());
		}
		finally{
			try {
				if(con!=null)con.close();
				if(s!=null)s.close();
				if(rs!=null)rs.close();
			} catch (Exception e2) {
				System.out.println(e2.getMessage());
			}
		}

		return array;
		
	}

}
